﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Common;
using DataModel;
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using ProjectManager.User;
using ProjectManager.User.Model;
using FangPage.Common;
using System.Drawing;
using System.IO;

namespace ProjectManagerSys
{
    public class ExportController : Controller
    {
        [HttpGet]
        public FileResult UserStatisExport()
        {
            string downloadFileName = DateTime.Now.Date.ToString("yyyy-MM-dd") + "-员工任务统计.xls";
            const string ctxType = "application/vnd.ms-excel";
            byte[] dataExcel = CreateStatisExcel();
            return File(dataExcel, ctxType, downloadFileName);
        }

        #region 导出员工统计

        
        /// <summary>
        /// 构建任务统计Excel表格
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private byte[] CreateStatisExcel()
        {
            byte[] dataExcel = null;
            HSSFWorkbook book = new HSSFWorkbook();
            using (System.IO.MemoryStream msExcel = new System.IO.MemoryStream())
            {
                ISheet sheet = book.CreateSheet("员工任务统计");
                sheet.DefaultColumnWidth = 15;

                //样式
                ICellStyle title_style = GetCellStyle(book, "title");

                ICellStyle th_style = GetCellStyle(book, "fonthead");

                ICellStyle cellstyle = GetCellStyle(book, "");

                ICellStyle is_style = GetCellStyle(book, "is_num");

                //标题
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
                NPOI.SS.UserModel.IRow rowt = sheet.CreateRow(0);
                rowt.Height = 56 * 10;
                var title0 = rowt.CreateCell(0);
                title0.SetCellValue("员工任务统计");
                title0.CellStyle = title_style;

                //表头
                NPOI.SS.UserModel.IRow rowh = sheet.CreateRow(1);
                rowh.Height = 60 * 10;
                SetCellValue(rowh, 0, "姓名", th_style);
                sheet.SetColumnWidth(0, 20 * 256 + 200);//列宽
                //sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));//单元格合并（起始行号，终止行号， 起始列号，终止列号）
                SetCellValue(rowh, 1, "任务总数", th_style);
                SetCellValue(rowh, 2, "未完成", th_style);
                SetCellValue(rowh, 3, "已完成", th_style);
                SetCellValue(rowh, 4, "提前完成", th_style);
                SetCellValue(rowh, 5, "逾期完成", th_style);
                SetCellValue(rowh, 6, "个人能力(%)", th_style);
                SetCellValue(rowh, 7, "饱和度", th_style);

                const int contentRow = 2;//内容起始行Index
                var dt = new IndexMerber().UsersStatis2(); //任务统计
                var perSat = new IndexMerber().CalculatePersonSat2(); //个人能力

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rr = sheet.CreateRow(i + contentRow);
                    rr.Height = 50 * 10;
                    SetCellValue(rr, 0, dt.Rows[i]["realname"].ToString(), cellstyle);
                    SetCellValue(rr, 1, dt.Rows[i]["alltotal"].ToString(), cellstyle);
                    SetCellValue(rr, 2, dt.Rows[i]["taskno"].ToString(), cellstyle);
                    SetCellValue(rr, 3, dt.Rows[i]["finishtask"].ToString(), cellstyle);
                    SetCellValue(rr, 4, dt.Rows[i]["taskup"].ToString(), cellstyle);
                    SetCellValue(rr, 5, dt.Rows[i]["overtask"].ToString(), GetCellStyle(book, "red"));
                    SetCellValue(rr, 6, Math.Round((decimal)dt.Rows[i]["uppercent"]).ToString(), cellstyle);
                    var sat = "0";
                    var calsum = (Convert.ToDecimal(dt.Rows[i]["totaltimes"]) + Convert.ToDecimal(dt.Rows[i]["overtimes"]));
                    if (calsum > 0)
                    { //分子大于0
                        foreach (DataRow item in perSat.Rows)
                        {
                            if (dt.Rows[i]["id"].ToString().Trim() == item["id"].ToString().Trim())
                            {
                                if (Convert.ToDecimal(item["alltimes"]) > 0)
                                { //分母大于0
                                    //计算出能力值
                                    decimal new_sat = (calsum / Convert.ToDecimal(item["alltimes"]));
                                    sat = decimal.Round(new_sat,2).ToString();
                                    break;
                                }
                            }
                        }
                    }

                    SetCellValue(rr, 7, sat, cellstyle);
                }

                book.Write(msExcel);
                msExcel.Position = 0;
                dataExcel = msExcel.ToArray();
            }
            return dataExcel;
        }
        #endregion


        [HttpGet]
        public FileResult UserTaskExport(string taskname, string leadername,
            string membername, DateTime? startTime, DateTime? endTime, string projectname, string status)
        {
            List<MANAGER_TASK> tasklist = new List<MANAGER_TASK>();
            string downloadFileName = "";
            string where = " ";
            try
            {
                using (var con = Data.DB.GetConnection())
                {
                    var list = con.MANAGER_TASK.Where(p => p.ACTIVE == 1);
                    //权限
                    AuthInfo authinfo = Logins.Authorize();
                    if (authinfo.isadmin != 1)
                    {
                        var new_user = "," + authinfo.userid + ",";
                        if (string.IsNullOrEmpty(authinfo.departs)) //个人
                        {
                            downloadFileName += authinfo.realname + "-";
                            list = list.Where(c => c.TASKLEADER.Contains(new_user) || c.TASKMEMBER.Contains(new_user));
                        }
                        else //部门
                        {
                            downloadFileName += authinfo.departname + "-";
                            var arr_depart = authinfo.departid.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                            list = list.Where(c => arr_depart.Contains(c.DEPARTCODE) || arr_depart.Contains(c.UPDATECODE) || c.TASKLEADER.Contains(new_user) || c.TASKMEMBER.Contains(new_user) || c.APPROVALUSER.Contains(new_user));
                        }
                        //list = list.Where(c => c.CREATENAME == authinfo.userid || c.UPDATENAME == authinfo.userid || c.TASKLEADER.Contains(new_user) || c.TASKMEMBER.Contains(new_user));
                    }

                    if (string.IsNullOrWhiteSpace(projectname) == false) //项目名称
                    {
                        list = list.Where(p => p.PROJECTNAME.Contains(projectname));
                        where += string.Format(" and b.PROJECTNAME like '%{0}%' ",projectname);
                    }

                    if (string.IsNullOrWhiteSpace(taskname) == false) //任务名称
                    {
                        list = list.Where(p => p.TASKNAME.Contains(taskname));
                        where += string.Format(" and b.TASKNAME like '%{0}%' ",taskname);
                    }

                    if (string.IsNullOrWhiteSpace(leadername) == false)
                    {
                        list = list.Where(p => p.LEADERNAME.Contains(leadername));
                        where += string.Format(" and b.LEADERNAME like '%{0}%' ",leadername);
                    }

                    if (string.IsNullOrWhiteSpace(membername) == false)
                    {
                        list = list.Where(p => p.MEMBERNAME.Contains(membername));
                        where += string.Format(" and b.MEMBERNAME like '%{0}%' ", membername);
                    }

                    if (string.IsNullOrWhiteSpace(status) == false) // 任务状态
                    {
                        list = list.Where(p => p.TASKSTATUS == Convert.ToInt32(status));
                        where += string.Format(" and b.TASKSTATUS={0} ", status);
                    }
                        
                    if (startTime.HasValue && endTime.HasValue)
                    {
                        list = list.Where(p => p.TASKSTARTTIME >= startTime && p.TASKENDTIME <=Convert.ToDateTime(endTime).AddDays(1) || (p.TASKSTARTTIME >= startTime && p.TASKSTARTTIME<= Convert.ToDateTime(endTime).AddDays(1)));
                        downloadFileName +=Convert.ToDateTime(startTime).ToString("yyyy年MM月dd日") + "-" + Convert.ToDateTime(endTime).ToString("yyyy年MM月dd日") + "-";
                        where += string.Format(" and (b.TASKSTARTTIME>=to_date('{0}', 'yyyy-mm-dd hh24:mi:ss') and b.TASKENDTIME<=to_date('{1}', 'yyyy-mm-dd hh24:mi:ss') or b.TASKSTARTTIME>=to_date('{0}', 'yyyy-mm-dd hh24:mi:ss') and b.TASKSTARTTIME<=to_date('{1}', 'yyyy-mm-dd hh24:mi:ss') )", startTime, Convert.ToDateTime(endTime).AddDays(1));
                    }
                    else downloadFileName +=DateTime.Now.Date.ToString("yyyy年MM月dd日-");


                    tasklist = list.OrderBy(p => p.TASKSTARTTIME).ToList();

                }
            }
            catch (Exception ex) { }
            
            downloadFileName +=  "任务详情.xls";
            string ctxType = "application/vnd.ms-excel";
            byte[] dataExcel = CreateTaskExcel(tasklist, downloadFileName, where,startTime,endTime);
            return File(dataExcel, ctxType, downloadFileName);
        }
        #region 导出任务详情
        /// <summary>
        /// 构建任务统计Excel表格
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private byte[] CreateTaskExcel(List<MANAGER_TASK> tasklist,string title,string where,DateTime? start,DateTime? end)
        {
            byte[] dataExcel = null;
            HSSFWorkbook book = new HSSFWorkbook();
            using (System.IO.MemoryStream msExcel = new System.IO.MemoryStream())
            {
                ISheet sheet = book.CreateSheet(title);
                sheet.DefaultColumnWidth = 15;

                //样式
                ICellStyle title_style = GetCellStyle(book, "title");

                ICellStyle th_style = GetCellStyle(book, "fonthead");

                ICellStyle cellstyle = GetCellStyle(book, "");

                ICellStyle is_style = GetCellStyle(book, "is_num");

                //标题
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
                NPOI.SS.UserModel.IRow rowt = sheet.CreateRow(0);
                rowt.Height = 56 * 10;
                var title0 = rowt.CreateCell(0);
                title0.SetCellValue(title);
                title0.CellStyle = title_style;

                //表头
                NPOI.SS.UserModel.IRow rowh = sheet.CreateRow(1);
                rowh.Height = 60 * 10;
                SetCellValue(rowh, 0, "姓名", th_style);
                sheet.SetColumnWidth(0, 10 * 256);//列宽
                //sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));//单元格合并（起始行号，终止行号， 起始列号，终止列号）
                SetCellValue(rowh, 1, "任务总数", th_style);
                sheet.SetColumnWidth(1, 8 * 256);//列宽
                SetCellValue(rowh, 2, "未完成", th_style);
                sheet.SetColumnWidth(2, 8 * 256);//列宽
                SetCellValue(rowh, 3, "已完成", th_style);
                sheet.SetColumnWidth(3, 8 * 256);//列宽
                SetCellValue(rowh, 4, "提前完成", th_style);
                sheet.SetColumnWidth(4, 8 * 256);//列宽
                SetCellValue(rowh, 5, "逾期完成", th_style);
                sheet.SetColumnWidth(5, 8 * 256);//列宽
                SetCellValue(rowh, 6, "个人能力(%)", th_style);
                sheet.SetColumnWidth(6, 10 * 256);//列宽
                SetCellValue(rowh, 7, "饱和度", th_style);
                sheet.SetColumnWidth(7, 8 * 256);//列宽
                SetCellValue(rowh, 8, "任务名称", th_style);
                sheet.SetColumnWidth(8, 30 * 256);//列宽
                SetCellValue(rowh, 9, "任务开始时间", th_style);
                sheet.SetColumnWidth(9, 19 * 256);//列宽
                SetCellValue(rowh, 10, "任务结束时间", th_style);
                sheet.SetColumnWidth(10, 19 * 256);//列宽
                SetCellValue(rowh, 11, "任务状态", th_style);
                sheet.SetColumnWidth(11, 10 * 256);//列宽
                SetCellValue(rowh, 12, "设计工时", th_style);
                sheet.SetColumnWidth(12, 8 * 256);//列宽
                SetCellValue(rowh, 13, "实际工时", th_style);
                sheet.SetColumnWidth(13, 8 * 256);//列宽
                SetCellValue(rowh, 14, "逾期时长", th_style);
                sheet.SetColumnWidth(14, 8 * 256);//列宽
                SetCellValue(rowh, 15, "备注", th_style);
                sheet.SetColumnWidth(15, 50 * 256);//列宽

                //const int contentRow = 2;//内容起始行Index
                var dt = new IndexMerber().UsersStatis2(where); //任务统计
                var perSat = new IndexMerber().CalculatePersonSat2(start.ToString(),end.ToString()); //个人能力
                
                using (var con = Data.DB.GetConnection())
                {
                    var diclist = con.MANAGER_TASK_DIC.Where(c=>c.ACTIVE == 1).ToList(); //字典
                    var newIDs = tasklist.Select(c => c.ID).ToArray(); //任务ids
                    //评论和回复
                    var comoentlist = con.MANAGER_TASK_COMMENT.Where(c=>c.ACTIVE==1 && newIDs.Contains(c.RESOURCEID)).ToList().OrderBy(c=>c.CREATETIME);
                    int startRows = 2;
                    int endRows = 1;

                    int k = 0; //判断是否进入输出统计的情况条件
                    //再生成详细信息
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //属于该用户的任务
                        var new_user = "," + dt.Rows[i]["id"].ToString().Trim() + ",";
                        //数据库的值为null时，linq会产生异常
                        var newlist = tasklist.Where(c => c.TASKLEADER.Contains(new_user) || (c.TASKMEMBER == null ? "00000" : c.TASKMEMBER).Contains(new_user)).ToList();
                        //newlist = newlist.Where((x, i) => newlist.FindIndex(z => z.ID == x.ID) == i).ToList();
                        endRows = endRows + newlist.Count;
                        FPLog.Info("loginuser", "【CreateTaskExcel】:" + (startRows) + "-" + endRows);

                        for (var x = 0; x < newlist.Count; x++)
                        {
                            NPOI.SS.UserModel.IRow rr = sheet.CreateRow(startRows + x);
                            
                            FPLog.Info("loginuser", "【CreateTaskExcel】:" + (startRows + x));
                            rr.Height = 50 * 10;

                            #region //统计部分
                            if (i == k)
                            {
                                k++;
                                SetCellValue(rr, 0, dt.Rows[i]["realname"].ToString(), cellstyle);
                                SetCellValue(rr, 1, dt.Rows[i]["alltotal"].ToString(), cellstyle);
                                SetCellValue(rr, 2, dt.Rows[i]["taskno"].ToString(), cellstyle);
                                SetCellValue(rr, 3, dt.Rows[i]["finishtask"].ToString(), cellstyle);
                                SetCellValue(rr, 4, dt.Rows[i]["taskup"].ToString(), cellstyle);
                                SetCellValue(rr, 5, dt.Rows[i]["overtask"].ToString(), GetCellStyle(book, "red"));       
                                SetCellValue(rr, 6, Math.Round((decimal)dt.Rows[i]["uppercent"]).ToString(), cellstyle);
                                var sat = "0";
                                var calsum = (Convert.ToDecimal(dt.Rows[i]["totaltimes"]) + Convert.ToDecimal(dt.Rows[i]["overtimes"]));
                                if (calsum > 0)
                                {
                                    //分子大于0
                                    foreach (DataRow item in perSat.Rows)
                                    {
                                        if (dt.Rows[i]["id"].ToString().Trim() == item["id"].ToString().Trim())
                                        {
                                            if (Convert.ToDecimal(item["alltimes"]) > 0)
                                            { //分母大于0
                                              //计算出能力值
                                                decimal new_sat = (calsum / Convert.ToDecimal(item["alltimes"]));
                                                sat = decimal.Round(new_sat, 2).ToString();
                                                break;
                                            }
                                        }
                                    }
                                }
                                SetCellValue(rr, 7, sat, cellstyle);
                                //是否需要合并单元格
                                if (endRows > startRows)
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 0, 0));//单元格合并（起始行号，终止行号， 起始列号，终止列号）
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 1, 1));//单元格合并（起始行号，终止行号， 起始列号，终止列号）
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 2, 2));
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 3, 3));
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 4, 4));
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 5, 5));
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 6, 6));
                                    sheet.AddMergedRegion(new CellRangeAddress(startRows, endRows, 7, 7));
                                }
                                
                            }
                            
                            #endregion

                            SetCellValue(rr, 8, newlist[x].TASKNAME, cellstyle);
                            SetCellValue(rr, 9, Convert.ToDateTime(newlist[x].TASKSTARTTIME).ToString("yyyy-MM-dd HH:mm:ss"), cellstyle);
                            SetCellValue(rr, 10, Convert.ToDateTime(newlist[x].TASKENDTIME).ToString("yyyy-MM-dd HH:mm:ss"), cellstyle);
                            var newdic = diclist.Where(c => c.TYPE == 1 && c.CODE == newlist[x].TASKSTATUS.ToString()).FirstOrDefault();
                            SetCellValue(rr, 11, (newdic == null ? "" : newdic.CODENAME), cellstyle);
                            SetCellValue(rr, 12,(string.IsNullOrEmpty(newlist[x].TOTALTIME.ToString()) ? "0" : Math.Round((decimal)newlist[x].TOTALTIME,2).ToString()), cellstyle);
                            SetCellValue(rr, 13,(string.IsNullOrEmpty(newlist[x].REALTIME.ToString()) ? "0" : Math.Round((decimal)newlist[x].REALTIME,2).ToString()), cellstyle);
                            SetCellValue(rr, 14, (newlist[x].TOTALTIME < newlist[x].REALTIME ? decimal.Round(Convert.ToDecimal(newlist[x].REALTIME) - Convert.ToDecimal(newlist[x].TOTALTIME), 2).ToString() : "0"), GetCellStyle(book, "red"));
                            var newCommentList = comoentlist.Where(c => c.RESOURCEID == newlist[x].ID).ToList();
                            string remark = "";
                            //ICellStyle notesStyle = book.CreateCellStyle();
                            //notesStyle.WrapText = true;//设置换行这个要先设置
                            //ICell notesTitle = rr.CreateCell(0);
                            if (newCommentList.Count > 0)
                            {
                                //拼接成字符串
                                foreach (var comment in newCommentList)
                                {
                                    remark += string.Format("({0}){1}:{2}\n", comment.CREATETIME.ToString(), comment.REALNAME, FPUtils.RemoveHtml(comment.DETAIL));
                                }

                            }
                            SetCellValue(rr, 15, remark, cellstyle);
                        }
                        startRows = startRows + newlist.Count;
                        //if (i == 0) startRows = startRows + 1;
                        //notesTitle.CellStyle = notesStyle;//设置换行
                    }


                }

                book.Write(msExcel);
                msExcel.Position = 0;
                dataExcel = msExcel.ToArray();
            }
            return dataExcel;
        }
        #endregion

        #region 获取样式
        /// <summary>
        /// 获取样式
        /// </summary>
        /// <param name="book"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public ICellStyle GetCellStyle(HSSFWorkbook book, string name)
        {
            ICellStyle style = book.CreateCellStyle();
            IFont font = book.CreateFont();
            switch (name)
            {
                case "title"://标题
                    {

                        font.IsBold = true;//粗体
                        font.FontHeightInPoints = 16;
                        style.SetFont(font);
                    }
                    break;
                case "ctable"://制表
                    {

                    }
                    break;
                case "fonthead"://表头的字体
                    {
                        font.IsBold = true;
                        style.SetFont(font);
                    }
                    break;
                case "greyhead"://灰色表头
                    {
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                        style.WrapText = true;
                    }
                    break;
                case "bluehead"://蓝色表头
                    {
                        style.FillForegroundColor = 40;// NPOI.HSSF.Util.HSSFColor.AQUA.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "headch"://子表头
                    {
                        style.FillForegroundColor = 42;//NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "is_num"://是否或序号
                    {
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "red"://设置红色字体
                    {
                        font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;//设置文字颜色
                        style.SetFont(font);
                    }
                    break;
                default://单元格
                    {
                        style.VerticalAlignment = VerticalAlignment.Center;
                        style.Alignment = HorizontalAlignment.Center;
                        style.WrapText = true;
                    }
                    break;
            }
            //上下左右边框
            /*style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;*/
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            style.Alignment = HorizontalAlignment.Center;//水平居中
            return style;
        }
        #endregion

        #region 设置列的值
        /// <summary>
        /// 设置列的值
        /// </summary>
        /// <param name="rr">IRow行对象</param>
        /// <param name="cellIndex">列Index</param>
        /// <param name="value">值</param>
        /// <param name="style">样式</param>
        public void SetCellValue(IRow rr, int cellIndex, string value, ICellStyle style)
        {
            var cell = rr.CreateCell(cellIndex);
            cell.SetCellValue(value);
            cell.CellStyle = style;
        }
        #endregion

        /// <summary>
        /// 个人绩效导出
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        [HttpGet]
        public FileResult UserAchievementsExport(DateTime? startTime, DateTime? endTime)
        {
            List<MANAGER_TASK> tasklist = new List<MANAGER_TASK>();
            string downloadFileName = "";
            string where = " ";
            try
            {
                using (var con = Data.DB.GetConnection())
                {
                    var list = con.MANAGER_TASK.Where(p => p.ACTIVE == 1);
                    //权限
                    AuthInfo authinfo = Logins.Authorize();
                    var new_user = "," + authinfo.userid + ",";
                    downloadFileName += authinfo.realname + "-";
                    list = list.Where(c => c.TASKLEADER.Contains(new_user) || c.TASKMEMBER.Contains(new_user));

                    if (startTime.HasValue && endTime.HasValue)
                    {
                        list = list.Where(p => p.TASKSTARTTIME >= startTime && p.TASKENDTIME <= Convert.ToDateTime(endTime).AddDays(1) || (p.TASKSTARTTIME >= startTime && p.TASKSTARTTIME <= Convert.ToDateTime(endTime).AddDays(1)));
                        downloadFileName += Convert.ToDateTime(startTime).ToString("yyyy年MM月dd日") + "-" + Convert.ToDateTime(endTime).ToString("yyyy年MM月dd日") + "-";
                    }
                    else downloadFileName += DateTime.Now.Date.ToString("yyyy年MM月dd日-");


                    tasklist = list.OrderBy(p => p.TASKSTARTTIME).ToList();

                }
            }
            catch (Exception ex) { }

            downloadFileName += "任务详情.xls";
            string ctxType = "application/vnd.ms-excel";
            byte[] dataExcel = CreateTaskExcel(tasklist, downloadFileName, where, startTime, endTime);
            return File(dataExcel, ctxType, downloadFileName);
        }

        public byte[] AchievementsExcel(List<MANAGER_TASK> tasklist, string title)
        {
            byte[] dataExcel = null;
            HSSFWorkbook workbook = new HSSFWorkbook();
            using (System.IO.MemoryStream msExcel = new System.IO.MemoryStream())
            {
                ICellStyle cellstyle = GetCellStyle(workbook, "");
                string excelPath = FPPath.GetMapPath("/") + "document/jixiao_model.xlsx";
                if (!Directory.Exists(excelPath)) //判断文件是否存在,不存在时则创建并添加表头
                {

                }
                else
                {   //如果文件已存在则首先打开，并且获取到最大的行数
                    using (FileStream fs = new FileStream(excelPath, FileMode.OpenOrCreate))//读取流
                    {
                        workbook = new HSSFWorkbook(fs);
                        ISheet sheet1 = workbook.GetSheetAt(0);
                        int num = 7;//获取最大行数

                        FileStream fout = new FileStream(excelPath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流

                        //获取到已存在的sheet
                        ISheet sheet = workbook.GetSheet("log");
                        //获取任务标签的列表
                        List<MANAGER_TASK_DIC> dlist = new List<MANAGER_TASK_DIC>();
                        using (var con = Data.DB.GetConnection())
                        {
                            dlist = con.MANAGER_TASK_DIC.Where(c => c.ACTIVE == 1 && c.TYPE == 2).ToList();
                        }

                        for (int i = 0; i < tasklist.Count; i++)
                        {
                            //创建行数时+num
                            IRow rr = sheet.CreateRow(i + num);
                            SetCellValue(rr, 0, tasklist[i].PROJECTNAME.ToString(), cellstyle);
                            var tipname = "";
                            if (!string.IsNullOrEmpty(tasklist[i].TASKTIP))
                            {
                                string[] tip_arr = tasklist[i].TASKTIP.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                                var newlist = dlist.Where(c => tip_arr.Contains(c.CODE)).ToList();
                                tipname = newlist.Select(c => c.CODENAME).ToString();
                            }
                            SetCellValue(rr, 1, tipname, cellstyle);
                            SetCellValue(rr, 2, tasklist[i].TASKNAME.ToString(), cellstyle);
                        }
                        //workbook.Write(fout);
                        workbook.Write(msExcel);
                        msExcel.Position = 0;
                        dataExcel = msExcel.ToArray();
                        fs.Close();
                        fout.Close();
                    }
                }

            }
            return dataExcel;
        }

    }
}